<?php // lib/dbinterface.php

define('DB_QUERYCACHE', false);

class DB {
	public static $Settings;

	public static function Connect($Database, $User=null, $Password=null) {
		if ($User) $Database = array("server"=>$Database, "username"=>$User, "password"=>$Password);
		
		self::$Settings = $Database;
		$connect = mysql_connect($Database["server"], $Database["username"], $Database["password"]);
		return echosys("CONNECT MySQL : localhost", ($connect) ? MSG_QUERY : MSG_FQUERY, mysql_error());
	}

	public static function SetUTF8() {
		self::SetCharset("utf8", "utf8_bin");
	}

	public static function SetCharset($charset, $collation) {
		self::Query("SET NAMES `$charset`;");
		self::Query("SET CHARACTER SET $charset;");
		self::Query("SET COLLATION_CONNECTION=`$collation`;");
	}

	public static function SelectDB($DatabaseName=null) {
		if ($DatabaseName) self::$Settings['database'] = $DatabaseName;
		else $DatabaseName = self::$Settings['database'];
		return echosys("SELECT DB : $DatabaseName", (@mysql_select_db($DatabaseName)) ? MSG_QUERY : MSG_FQUERY, mysql_error());
	}

	public static function Query($SQL) {
		$Table = SQL::FindTableFromSQL($SQL);
		$timer = Timer::Dummy();
		$Result = @mysql_query($SQL);
		$_elapsed = round($timer->End() * 1000);	// in ms
		
		echosys($SQL." ($_elapsed ms)", ($Result) ? MSG_QUERY : MSG_FQUERY, mysql_error());
		
		if (SQL::isEffecting($SQL)) Cache::ClearCacheGroup($Table);
		
		return $Result;
	}
	
	public static function GetPrimaryKey($Table) {
		return "id"; // just for now // for compability
	}
	
	public static function PopulateRelation($Dump, $SourceFieldName, $TargetTableName, $TargetFieldName = "id", $FieldsToBring = "*") {
		if ($Dump["id"]) { $DumpDepth = 1; $Dump = array($Dump); }
		elseif ($Dump[0]["id"]) $DumpDepth = 2;
		
		foreach ($Dump as $_no => $_row) {
			$keyval = $_row[$SourceFieldName];
			$Res = DB::DumpQuery("select $FieldsToBring from $TargetTableName where $TargetFieldName = $keyval;");
			if ($Res)
				$Dump[$_no][$SourceFieldName] = $Res[0];
			else
				$Dump[$_no][$SourceFieldName] = false;
		}
		
		return ($DumpDepth==1) ? $Dump[0] : $Dump;
	}
	
	public static function WherePhase($Tablename, $Match, $Operator = "=", $Start = "where ") {
		$sql = ($Start=="where ") ? $Start : $Start." and";
		foreach ($Match as $_key => $_value)
			$sql .= " $Tablename.$_key $Operator '$_value' and";
		$sql = substr($sql, 0, -4);
		return $sql;
	}
	
	public static function Update($Table, $Fields, $Values = null) {
		
		
		$NewData = array();
		$Data = parse_relaxarray($Fields, $Values);
		if ($Data['id']) { $IdToUpdate = $Data['id']; unset($Data['id']); }
		foreach ($Data as $_key => $_val)
			$NewData[$Table.".".$_key] = mysql_escape_string($_val);

		$sql = "update ".$Table." set ";
		foreach ($NewData as $_key => $_val)
			$sql .= "$_key='$_val',";
		$sql = substr($sql, 0, -1);
				
		$sql .= " where id='".$IdToUpdate."'";
		
		if (DB::Query($sql))
			return true;
		else
			return false;
	}
	
	public static function GetFields($Table, $Details = false) {
		$data = self::DumpQuery("describe $Table");
		if (!$data) return false;
		if ($Details) return $data;
		$names = array();
		foreach ($data as $field) $names[] = $field["Field"];
		return $names;
	}
	
	public static function CheckTable ($Table) {
		return (self::Query("describe `{$Table}`;")) ? true : false;
	}
	
	public static function Insert($Table, $Fields, $Values = null, $UnsetIDIfExists = true) {
		//
		$tablefields = self::GetFields($Table);
		
		$NewData = array();
		$Data = parse_relaxarray($Fields, $Values);
		$Key = self::DetectPrimaryKey($Table, true);
		//if (strpos($Key["Extra"], "auto_increment")!==false)
		if (($Data[$Key["Field"]])&&($UnsetIdIfExists)) unset($Key["Field"]);
		foreach ($Data as $_key => $_val)
			if (is_array($tablefields)&&array_search($_key, $tablefields)!==false) $NewData["$Table.$_key"] = $_val;
		
		$sql = "insert into ".$Table;
		$sql .= " (".implode(',', array_keys($NewData)).")";
		$sql .= " values(";
		foreach ($NewData as $_key => $_value) {
			if (substr($_value,0,2)=="!!") {
				$sql .= mysql_escape_string(substr($_value, 2)).",";
			}else{
				$sql .= "'".mysql_escape_string($_value)."',";				
			}
		}
			
		$sql = substr($sql, 0, -1);
		$sql .= ");";

		if ($NewID = DB::Query($sql))
			return self::IdOfLastInsert();
		else
			return false;
	}
	
	/**
	 * Inserts a row into table and return id
	 *	*** does not work if there is 'not null' fields ***
	 */
	public static function InsertEmptyRecord($Table) {
		
		
		$sql = "insert into {$Table} values();";
		if (DB::Query($sql)) {
			return self::IdOfLastInsert();
		}else{
			return false; // Query Failed
		}
	}
	
	/*
		DataToMatch
			array( "field"=>"value",
					"otherfield"=>1,
					);
	*/
	public static function Lookup($Table, $DataToMatch = array(), $FieldToReturn = null) {
		
		$sql = "select * from {$Table}";
		if (sizeof($DataToMatch)>0) $sql .= " where";
		
		foreach ($DataToMatch as $_field => $_value)
			//if (is_numeric($_value))
			//	$sql .= " {$Table}.{$_field}={$_value}";
			//else 
				$sql .= " {$Table}.{$_field}='{$_value}' and";
		
		$sql = substr($sql,0,-4);
		if ($_res = self::Query($sql)) {
			if ($res = self::Fetch($_res))
				return $FieldToReturn ? $res[$FieldToReturn] : $res;
			else
				return false;
		}else{
			return false;
		}
	}
	
	/* Name can change to lookup soon */
	/* Value should be string (at least for now) */
	public static function SearchRow($Table, $Field, $Value, $FieldToReturn = null) {
		
		$sql = "select * from {$Table} where $Field like '{$Value}';";
		if ($_res = self::Query($sql)) {
			if ($res = self::Fetch($_res))
				return $FieldToReturn ? $res[$FieldToReturn] : $res;
			else
				return false;
		}else{
			return false;
		}
	}
	
	public static function DetectPrimaryKey($Table, $Details = false) {
		$data = self::DumpQuery("describe $Table");
		if (!$data) return false;
		foreach ($data as $field) if ($field["Key"]=="PRI") return $Details ? $field : $field["Field"];
	}
	
	public static function GetRow($Table, $Id) {
		
		$Id = mysql_escape_string($Id);
		$sql = "select * from {$Table} where id={$Id};";
		if ($_res = self::Query($sql))
			return ($res = self::Fetch($_res)) ? $res : false;
		else
			return false;
	}
	
	public static function GetRowBy($Table, $Field, $Value) {
		
		$sql = "select * from {$Table} where $Field='{$Value}';";
		if ($_res = self::Query($sql))
			return ($res = self::Fetch($_res)) ? $res : false;
		else
			return false;
	}
	
	public static function DeleteRow($Table, $Id) {
		
		$sql = "delete from {$Table} where id={$Id};";
		return self::Query($sql);
	}
	
	public static function GetItem($Table, $Id, $Field) {
		
		$RowData = self::GetRow($Table, $Id);
		return $RowData ? $RowData[$Field] : false;
	}
	
	public static function SetItem($Table, $Id, $Field, $Value, $isInteger = false) {
		
		$sql = $isInteger ? 
					"update {$Table} set {$Table}.{$Field}={$Value} where id={$Id};":
					"update {$Table} set {$Table}.{$Field}='{$Value}' where id={$Id};";
		return self::Query($sql);
	}
	
	public static function ReplaceAll($Table, $Field, $StringToFind, $StringToPut) {
		
		$sql = "update {$Table} set {$Table}.{$Field}=REPLACE({$Table}.{$Field}, '{$StringToFind}', '{$StringToPut}');";
		return self::Query($sql);
	}

	public static function Error() {
		return @mysql_error();
	}

	public static function AffectedRowCount() {
		return @mysql_affected_rows();
	}

	public static function IdOfLastInsert() {
		return @mysql_insert_id();
	}

	public static function ListDatabases() {
		return @mysql_list_dbs();
	}

	public static function ListTables($DatabaseName=null) {
		if (!$DatabaseName) $DatabasaeName = self::$Settings["database"];
		return @mysql_list_tables($DatabaseName);
	}

	public static function ListFields($DatabaseName, $Table) {
		
		return @mysql_list_fields($DatabaseName, $Table);
	}

	public static function DumpFieldList($Table, $Details = false, $KeyNameOrder = false) {
		
		if ($Result = self::Query("describe {$Table};")) {
			$ResultArray = array();
			while ($a = self::Fetch($Result)) {
				if (!$KeyNameOrder) {
					array_push($ResultArray, $Details ? $a : $a['Field']);
				}else{
					$ResultArray[$a['Field']] = $a;
				}
			}
			return $ResultArray;
		}else{
			return false;
		}
	}

	public static function RowCountOf($Result) {
		return @mysql_num_rows($Result);
	}

	public static function ColCountOf($Result) {
		return @mysql_num_fields($Result);
	}

	public static function Fetch($Result) {
		return @mysql_fetch_assoc($Result);
	}
	
	public static function FetchNumeric($Result) {
		return @mysql_fetch_row($Result);
	}
	
	public static function Dump($Result) {
		$returning = array();
		while ($_temp = self::Fetch($Result))
			$returning[] = $_temp;
		return $returning;
	}

	public static function Dump_OneKey($Result) {
		$returning = array();
		while ($_temp = self::FetchNumeric($Result))
			$returning[] = $_temp[0];
		return $returning;
	}

	public static function Dump_OneRow($Result) {
		return self::Fetch($Result);
	}

	public static function Dump_OneRowNumeric($Result) {
		return self::FetchNumeric($Result);
	}

	// IndexStyle's
	//              - forcestd,std = return array(0 => row1_assoc_array, 1=> row2_assoc_array)..
	//              - onekey = return array(0 => row1.col1, 1=> row2.col1, 2=> row3.col1)...
	//              more can be added
	public static function DumpQuery($Query, $IndexStyle = "forcestd") {
		$Table = SQL::FindTableFromSQL($Query);
		//if (defined('DB_QUERYCACHE') && ($Table!=false)) {
		if ($Data = Cache::GetCache($Table."_".$IndexStyle, $Query)&&DB_QUERYCACHE) {
			echosys("$Query (cached)", MSG_QUERY);
			return $Data;
		}else{
			if ($IndexStyle=="forcestd")
				$Data = self::Dump(self::Query($Query));
			elseif ($IndexStyle=="onekey")
				$Data = self::Dump_OneKey(self::Query($Query));
			elseif ($IndexStyle=="onerow")
				$Data = self::Dump_OneRow(self::Query($Query));
			elseif ($IndexStyle=="onerownumeric")
				$Data = self::Dump_OneRowNumeric(self::Query($Query));

			if ($Data&&DB_QUERYCACHE) Cache::Save($Table."_".$IndexStyle, $Query, $Data);
			return $Data;
		}
	}
	
	public static function GetOneByQuery($Query) {
		$s = self::DumpQuery($Query, "onekey");
		if ($s) return $s[0]; else return false;
	}

	public static function SeekIn($RowNumber, $Result) {
		return @mysql_data_seek($Result, $Rownumber);
	}
	/*
	public static function CheckTable($Table, $CompareSQL = null, $Create = false, $DropIfWrong = false) {
		if (!$CompareSQL) return self::SQLDumpStructure($Table);
		
		$TableSQL = self::SQLDumpStructure($Table);
		if (String::Compare($TableSQL, $CompareSQL, 5)) return true;
		
		if ($Create) {
			if ($DropIfWrong) { self::Query("DROP TABLE IF EXISTS `".self::$UsePrefix.$Table."`;"); }
			$create = self::Query($CompareSQL);
			return self::SQLDumpStructure($Table);
		}
		
		return false;
	}
	*/
	public static function DropTable($Table) {
		
		return self::Query("DROP TABLE `$Table`;");
	}
		
	public static function SQLDumpStructure($Table) {
		
		// # SQL
		$sql = "show create table {$Table};";
		// # Query
		if ($res = self::Query($sql)) {
			$_res = DB::Fetch($res);
			return $_res["Create Table"].";\n";
		}else{
			return false;
		}
	}
	
	public static function SQLDumpData($Table) {
		
		$sql = "select * from {$Table};";
		if ($res=DB::Query($sql)) {
			$_res = DB::Dump($res);
			$retsql = "\n";
			foreach ($_res as $_row) {
				$cols = array_putin("`%%%`", array_keys($_row));
				$values = array_putin("'%%%'", array_values($_row), true);
				$retsql .= "insert into `{$Table}` (".implode(", ", $cols).") values(";
				$retsql .= implode(", ", $values).");\n";
			}
			return $retsql;
		}else{
			return false;	// query failed
		}
	}
	
	public static function CheckSchema($SchemaFile) {
		// SCHEMA CHECK
	}
}

class SQL {
	public $SQL;
	public $Ready = false;
	
	function Give() {
		return $this->Ready ? $this->SQL : false;
	}
	
	public static function isEffecting($sql) {
		$sql = strtolower(trim($sql));
		$cmd = explode(" ", $sql);
		$effecters = array("insert", "delete", "update");
		return (in_array($cmd[0], $effecters));
	}
	
	public static function FindTableFromSQL($sql) {
		$cmdn = explode(" ", trim($sql));
		$sql = strtolower(trim($sql));
		$cmd = explode(" ", $sql);
		switch ($cmd[0]) {
			case 'update':
				return $cmd[1];
				break;
			
			case 'delete':
				$frompos = array_search("from", $cmd);
				return $cmdn[$frompos+1];
				break;
			
			case 'select':
				$frompos = array_search("from", $cmd);
				return $cmdn[$frompos+1];
				break;
			
			case 'insert':
				$intopos = array_search("into", $cmd);
				return $cmdn[$intopos+1];
				break;
			
			// create
			// drop
		}
	}
}

class CreateSQL extends SQL {
	public $Table;
	public $Fields = array();
	public $PrimaryKey = null;
	private $_LastType;
	private $_LastOptions;
	
	function __construct($Table) {
		//$this->Table = $Table = DB::$UsePrefix.$Table;
		$this->SQL = "CREATE TABLE `$Table` (\n";
	}
	
	function addField($Name, $Type = null, $Options = null) {
		if ($Type==null) $Type = $this->_LastType;
		if ($Options==null) $Options = $this->_LastOptions;
		
		$this->_LastType = $Type;
		$this->_LastOptions = $Options;
		
		$Options = parse_relaxarray($Options);
		if (!is_array($Options)) $Options = array();
		
		$this->Fields[] .= "\t`$Name` $Type ".implode(' ', $Options);
	}
	
	function done() {
		$this->SQL .= implode(",\n", $this->Fields);
		$this->SQL .= $this->PrimaryKey ? ",\n\tPRIMARY KEY (`".$this->PrimaryKey."`)\n" : "\n";
		$this->SQL .= ") ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin";
		$this->Ready = true;
		return $this->SQL;
	}
}
/*
class DBSchema {
	public static function GetSchema($File) {
		$data = Config::ParseFile($File);
		if (!$data) return false;	// Cannot Read Schema File
		//print_r($data);
		foreach ($data as $name=>$fields)
			echo(self::TableSQL($name, $fields)."<br />\n");
	}
	
	public static function GenerateSchema($Table) {
		$res = array();
		$structure = DB::DumpFieldList($Table, true, true);
		if (!$structure) return false;
		foreach ($structure as $fname => $opts) {
			$res[$Table][$fname] = _type2schema($opts);
		}
		return $res;
		//print_r($structure);
	}
	
	public static function TableSQL($Name, $Fields) {
		$s = new CreateSQL($Name);
		foreach ($Fields as $f_name => $props) {
			if (substr($f_name,0,1)=="-") {
				$f_name = substr($f_name,1);
			}else{
				unset($fps);
				if ($f_name=="id") $fps = array("id");
				else $fps = String::SmartExplode(":", $props);

				unset($fieldtype);
				switch ($fps[0]) {
					case "id":
					$s->PrimaryKey = "id";
					$fieldtype = "int(10) NOT NULL auto_increment";
					break;
				
					case "idof":
					$fieldtype = "int(10) NOT NULL";
					break;
				
					case "char":
					case "varchar":
					$size = $fps[1];
					$fieldtype = "varchar(".$size.")";
					break;
				
					case "md5":
					$size = 32;	// md5 = 32 chars?
					$fieldtype = "varchar(".$size.")";
					break;
				
					case "int":
					case "unsigned int":
					case "bigint":
					case "tinyint":
					case "smallint":
					$fieldtype = $fps[0];
					$fieldtype .= ($fps[1]&&is_numeric($fps[1])) ? "(".$fps[1].")" : "";
					break;
				
					case "datetime":
					case "time":
					case "date":
					$fieldtype = $fps[0];
					break;
				
					case "bool":
					$fieldtype = "tinyint(1)";
					break;
				}
			
				if (in_array("unique", $fps)) $fieldtype .= " UNIQUE";
			
				$s->addField($f_name, $fieldtype);
			}
		}
		return $s->done();
	}
}

function _type2schema($type) {
	if (strpos($type["Type"], '(') !== false) {
		$tps = explode("(", $type["Type"]);
		// no, no, noT!
		$tpk = explode(",", substr($tps[1], 0, -1));
		return $tps[0].":".implode(":", $tpk);
	}else{
		return $type["Type"];
	}
}
*/

	
/**
 * Puts $Text into place of %%% sembol in $Format
 *
 * @param String $Format
 * @param String $Text
 * @param Boolean $MysqlEscape
 * @return String
 */
function putin($Format, $Text, $MysqlEscape=false) {
	if ($MysqlEscape)
		//$Text = mysql_escape_string($Text);
		$Text = self::LocalSQLEscape($Text);
		
	return str_replace("%%%", $Text, $Format);
}

function array_putin($Format, $Stash, $MysqlEscape=false) {
	if (is_array($Stash)) {
		foreach ($Stash as &$_st) {
			$_st = putin($Format, $_st, $MysqlEscape);
		}
		return $Stash;
	}
}
?>
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      